Learn T-SQL Commands with Samples
Skip Navigation Links
Skip Navigation Links.
Expand DatabaseDatabase
Expand TableTable
Expand ViewView
Expand Stored ProcedureStored Procedure
Expand Data FilteringData Filtering
Expand Data GroupingData Grouping
Expand JoinsJoins
Expand TriggerTrigger
Expand CursorCursor
Expand OperatorsOperators
Expand ConstraintsConstraints
Expand FunctionsFunctions
Expand Conditional ProcessingConditional Processing
Expand LoopingLooping
Expand Error HandlingError Handling
Expand v.IMP Queriesv.IMP Queries
Expand XMLXML
Expand Query PerformanceQuery Performance
Collapse QueriesQueries
Expand NormalizationNormalization
Expand CreateCreate
     

Recursive Queries

 
      
---- Using a common table expression to limit counts and report averages
The following example shows the average number of employees reporting to managers with Adventure Works database. USE AdventureWorks; GO WITH DirReps (Manager, DirectReports) AS ( SELECT ManagerID, COUNT(*) AS DirectReports FROM HumanResources.Employee GROUP BY ManagerID ) SELECT AVG(DirectReports) AS [Average Number of Direct Reports] FROM DirReps WHERE DirectReports >= 2 ; GO